const { pool, query: dbQuery } = require('../config/database');
const { logger } = require('../utils/logger');

/**
 * 菜单项模型类
 * 处理菜单相关的数据库操作
 */
class MenuItem {
  constructor(data = {}) {
    this.id = data.id;
    this.name = data.name;
    this.name_en = data.name_en;
    this.normalized_name = data.normalized_name;
    this.description = data.description;
    this.description_en = data.description_en;
    this.price = data.price;
    this.category_id = data.category_id;
    this.image_url = data.image_url;
    this.is_available = data.is_available !== undefined ? data.is_available : true;
    this.is_featured = data.is_featured !== undefined ? data.is_featured : false;
    this.preparation_time = data.preparation_time;
    this.calories = data.calories;
    this.spice_level = data.spice_level;
    this.allergens = data.allergens;
    this.ingredients = data.ingredients;
    this.main_ingredients = data.main_ingredients;
    this.main_ingredients_en = data.main_ingredients_en;
    this.quantity_selection = data.quantity_selection;
    this.nutritional_info = data.nutritional_info;
    this.tags = data.tags;
    this.sort_order = data.sort_order || 0;
    this.is_recommended = data.is_recommended !== undefined ? data.is_recommended : false;
    this.is_spicy = data.is_spicy !== undefined ? data.is_spicy : false;
    this.is_vegetarian = data.is_vegetarian !== undefined ? data.is_vegetarian : false;
    this.is_vegan = data.is_vegan !== undefined ? data.is_vegan : false;
    this.is_gluten_free = data.is_gluten_free !== undefined ? data.is_gluten_free : false;
    // 新增：是否为现金券商品（从数据库列 is_cash_voucher 读取，默认 false）
    this.is_cash_voucher = data.is_cash_voucher !== undefined ? Boolean(data.is_cash_voucher) : false;
    this.created_at = data.created_at;
    this.updated_at = data.updated_at;
  }

  /**
   * 创建新菜单项
   */
  static async create(menuItemData) {
    try {
      const {
        name, name_en, description, description_en, price, category_id,
        image_url, is_available, is_featured, preparation_time, calories,
        spice_level, allergens, ingredients, nutritional_info, tags, sort_order
      } = menuItemData;

      const result = await dbQuery(
        `INSERT INTO menu_items (
          name, name_en, description, description_en, price, category_id,
          image_url, is_available, is_featured, preparation_time, calories,
          spice_level, allergens, ingredients, nutritional_info, tags, sort_order
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          name,
          name_en ?? null,
          description ?? null,
          description_en ?? null,
          price,
          category_id,
          image_url ?? null,
          is_available ?? true,
          is_featured ?? false,
          preparation_time ?? null,
          calories ?? null,
          spice_level ?? 0,
          allergens !== undefined ? JSON.stringify(allergens) : null,
          ingredients !== undefined ? JSON.stringify(ingredients) : null,
          nutritional_info !== undefined ? JSON.stringify(nutritional_info) : null,
          tags !== undefined ? JSON.stringify(tags) : null,
          sort_order ?? 0
        ]
      );

      return await MenuItem.findById(result.insertId);
    } catch (error) {
      logger.error('创建菜单项失败:', error);
      throw error;
    }
  }

  /**
   * 根据ID查找菜单项
   */
  static async findById(id) {
    try {
      const rows = await dbQuery(
        'SELECT * FROM menu_items WHERE id = ? AND deleted_at IS NULL',
        [id]
      );

      if (rows.length === 0) {
        return null;
      }

      const menuItem = rows[0];
      // 安全解析JSON字段
      if (menuItem.allergens) {
        try {
          menuItem.allergens = JSON.parse(menuItem.allergens);
        } catch (e) {
          logger.warn(`解析allergens失败: ${e.message}`);
          menuItem.allergens = null;
        }
      }
      if (menuItem.ingredients) {
        try {
          menuItem.ingredients = JSON.parse(menuItem.ingredients);
        } catch (e) {
          // 如果解析失败，尝试将字符串转换为数组
          if (typeof menuItem.ingredients === 'string') {
            // 如果是简单的字符串，将其转换为单元素数组
            menuItem.ingredients = [menuItem.ingredients];
            logger.debug(`ingredients字段不是有效JSON，已转换为数组: ${menuItem.ingredients}`);
          } else {
            logger.warn(`解析ingredients失败: ${e.message}`);
            menuItem.ingredients = null;
          }
        }
      }
      if (menuItem.nutritional_info) {
        try {
          menuItem.nutritional_info = JSON.parse(menuItem.nutritional_info);
        } catch (e) {
          logger.warn(`解析nutritional_info失败: ${e.message}`);
          menuItem.nutritional_info = null;
        }
      }
      if (menuItem.tags) {
        try {
          menuItem.tags = JSON.parse(menuItem.tags);
        } catch (e) {
          logger.warn(`解析tags失败: ${e.message}`);
          menuItem.tags = null;
        }
      }

      return new MenuItem(menuItem);
    } catch (error) {
      logger.error('查找菜单项失败:', error);
      throw error;
    }
  }

  /**
   * 获取所有菜单项（支持分页和筛选）
   */
  static async findAll(options = {}) {
    try {
      const {
        page = 1,
        limit = 20,
        category_id,
        is_available,
        is_featured,
        search,
        sort_by = 'sort_order',
        sort_order = 'ASC'
      } = options;

      let query = 'SELECT * FROM menu_items WHERE deleted_at IS NULL';
      const params = [];

      // 添加筛选条件
      if (category_id) {
        query += ' AND category_id = ?';
        params.push(category_id);
      }

      if (is_available !== undefined) {
        query += ' AND is_available = ?';
        params.push(is_available);
      }

      if (is_featured !== undefined) {
        query += ' AND is_featured = ?';
        params.push(is_featured);
      }

      if (search) {
        query += ' AND (name LIKE ? OR name_en LIKE ? OR description LIKE ? OR description_en LIKE ?)';
        const searchTerm = `%${search}%`;
        params.push(searchTerm, searchTerm, searchTerm, searchTerm);
      }

      // 添加排序
      const validSortFields = ['id', 'name', 'price', 'sort_order', 'created_at', 'updated_at'];
      const sortField = validSortFields.includes(sort_by) ? sort_by : 'sort_order';
      const sortDirection = sort_order.toUpperCase() === 'DESC' ? 'DESC' : 'ASC';
      query += ` ORDER BY ${sortField} ${sortDirection}`;

      // 添加分页（注意：某些 MySQL 版本不支持在 LIMIT/OFFSET 中使用占位符）
      const offset = (page - 1) * limit;
      const limitInt = Math.max(0, parseInt(limit, 10) || 0);
      const offsetInt = Math.max(0, parseInt(offset, 10) || 0);
      query += ` LIMIT ${limitInt} OFFSET ${offsetInt}`;
      // params.push(limit, offset); // 改为内联，避免 ER_WRONG_ARGUMENTS

      const rows = await dbQuery(query, params);

      // 安全解析JSON字段
      const menuItems = rows.map(item => {
        if (item.allergens) {
          try {
            item.allergens = JSON.parse(item.allergens);
          } catch (e) {
            logger.warn(`解析allergens失败: ${e.message}`);
            item.allergens = null;
          }
        }
        if (item.ingredients) {
          try {
            item.ingredients = JSON.parse(item.ingredients);
          } catch (e) {
            // 如果解析失败，尝试将字符串转换为数组
            if (typeof item.ingredients === 'string') {
              // 如果是简单的字符串，将其转换为单元素数组
              item.ingredients = [item.ingredients];
              logger.debug(`ingredients字段不是有效JSON，已转换为数组: ${item.ingredients}`);
            } else {
              logger.warn(`解析ingredients失败: ${e.message}`);
              item.ingredients = null;
            }
          }
        }
        if (item.main_ingredients) {
          try {
            item.main_ingredients = JSON.parse(item.main_ingredients);
          } catch (e) {
            // 如果解析失败，尝试将字符串转换为数组
            if (typeof item.main_ingredients === 'string') {
              // 如果是简单的字符串，将其转换为单元素数组
              item.main_ingredients = [item.main_ingredients];
              logger.debug(`main_ingredients字段不是有效JSON，已转换为数组: ${item.main_ingredients}`);
            } else {
              logger.warn(`解析main_ingredients失败: ${e.message}`);
              item.main_ingredients = null;
            }
          }
        }
        if (item.nutritional_info) {
          try {
            item.nutritional_info = JSON.parse(item.nutritional_info);
          } catch (e) {
            logger.warn(`解析nutritional_info失败: ${e.message}`);
            item.nutritional_info = null;
          }
        }
        if (item.tags) {
          try {
            item.tags = JSON.parse(item.tags);
          } catch (e) {
            logger.warn(`解析tags失败: ${e.message}`);
            item.tags = null;
          }
        }
        return new MenuItem(item);
      });

      // 获取总数
      let countQuery = 'SELECT COUNT(*) as total FROM menu_items WHERE deleted_at IS NULL';
      const countParams = [];

      if (category_id) {
        countQuery += ' AND category_id = ?';
        countParams.push(category_id);
      }

      if (is_available !== undefined) {
        countQuery += ' AND is_available = ?';
        countParams.push(is_available);
      }

      if (is_featured !== undefined) {
        countQuery += ' AND is_featured = ?';
        countParams.push(is_featured);
      }

      if (search) {
        countQuery += ' AND (name LIKE ? OR name_en LIKE ? OR description LIKE ? OR description_en LIKE ?)';
        const searchTerm = `%${search}%`;
        countParams.push(searchTerm, searchTerm, searchTerm, searchTerm);
      }

      const countRows = await dbQuery(countQuery, countParams);
      const total = countRows[0].total;

      return {
        items: menuItems,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total,
          pages: Math.ceil(total / limit)
        }
      };
    } catch (error) {
      logger.error('获取菜单项列表失败:', error);
      throw error;
    }
  }

  /**
   * 根据分类获取菜单项
   */
  static async findByCategory(categoryId, options = {}) {
    return await MenuItem.findAll({ ...options, category_id: categoryId });
  }

  /**
   * 获取特色菜品
   */
  static async getFeatured(limit = 10) {
    return await MenuItem.findAll({ is_featured: true, limit });
  }

  /**
   * 软删除菜单项
   */
  static async delete(id) {
    try {
      const result = await dbQuery(
        'UPDATE menu_items SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL',
        [id]
      );

      return result.affectedRows > 0;
    } catch (error) {
      logger.error('删除菜单项失败:', error);
      throw error;
    }
  }

  /**
   * 更新菜单项可用性
   */
  static async updateAvailability(id, isAvailable) {
    try {
      const result = await dbQuery(
        'UPDATE menu_items SET is_available = ?, updated_at = NOW() WHERE id = ? AND deleted_at IS NULL',
        [isAvailable, id]
      );

      return result.affectedRows > 0;
    } catch (error) {
      logger.error('更新菜单项可用性失败:', error);
      throw error;
    }
  }

  /**
   * 更新菜单项
   */
  static async update(id, data = {}) {
    try {
      // 允许更新的列
      const allowedFields = {
        name: null,
        name_en: null,
        normalized_name: null,
        description: null,
        description_en: null,
        price: null,
        category_id: null,
        image_url: null,
        is_available: null,
        is_featured: null,
        is_recommended: null,
        is_spicy: null,
        is_vegetarian: null,
        is_vegan: null,
        is_gluten_free: null,
        preparation_time: null,
        calories: null,
        spice_level: null,
        allergens: null,
        ingredients: null,
        main_ingredients: null,
        main_ingredients_en: null,
        quantity_selection: null,
        nutritional_info: null,
        tags: null,
        sort_order: null
      };

      const setParts = [];
      const params = [];

      for (const key of Object.keys(allowedFields)) {
        if (Object.prototype.hasOwnProperty.call(data, key)) {
          let value = data[key];
          if (['allergens', 'ingredients', 'nutritional_info', 'tags'].includes(key)) {
            if (value !== null && value !== undefined) {
              // 如果传入的是字符串，尝试解析，否则按对象/数组序列化
              if (typeof value === 'string') {
                try {
                  value = JSON.stringify(JSON.parse(value));
                } catch (_) {
                  // 保持原样字符串
                }
              } else {
                value = JSON.stringify(value);
              }
            }
          }
          setParts.push(`${key} = ?`);
          params.push(value);
        }
      }

      if (setParts.length === 0) {
        // 没有可更新字段，返回当前记录
        return await MenuItem.findById(id);
      }

      const sql = `UPDATE menu_items SET ${setParts.join(', ')}, updated_at = NOW() WHERE id = ? AND deleted_at IS NULL`;
      params.push(id);

      const result = await dbQuery(sql, params);
      if (result.affectedRows === 0) {
        return null;
      }

      return await MenuItem.findById(id);
    } catch (error) {
      logger.error('更新菜单项失败:', error);
      throw error;
    }
  }

  /**
   * 批量更新排序
   */
  static async updateSortOrder(items) {
    try {
      const promises = items.map(item => 
        dbQuery(
          'UPDATE menu_items SET sort_order = ?, updated_at = NOW() WHERE id = ? AND deleted_at IS NULL',
          [item.sort_order, item.id]
        )
      );

      await Promise.all(promises);
      return true;
    } catch (error) {
      logger.error('批量更新排序失败:', error);
      throw error;
    }
  }

  /**
   * 搜索菜单项
   */
  static async search(keyword, options = {}) {
    return await MenuItem.findAll({ ...options, search: keyword });
  }

  /**
   * 获取菜单项统计信息
   */
  static async getStats() {
    try {
      const rows = await dbQuery(`
        SELECT 
          COUNT(*) as total,
          COUNT(CASE WHEN is_available = 1 THEN 1 END) as available,
          COUNT(CASE WHEN is_featured = 1 THEN 1 END) as featured,
          AVG(price) as avg_price,
          MIN(price) as min_price,
          MAX(price) as max_price
        FROM menu_items 
        WHERE deleted_at IS NULL
      `);

      return rows[0];
    } catch (error) {
      logger.error('获取菜单统计失败:', error);
      throw error;
    }
  }
}

module.exports = MenuItem;